package serverapp;

import java.sql.*;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author a
 */
public class Database {

    public static Connection con = null;
    public static Statement pst = null;
    public static String user = "root";
    public static String password = "";

    public static void connect() {
        String url = "jdbc:mysql://localhost/Siatka";
        try {
            con = DriverManager.getConnection(url, user, password);
            pst = con.createStatement();
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public static void disconnect() {
        try {
            if (pst != null) {
                pst.close();
            }
            if (con != null) {
                con.close();
            }
        } catch (SQLException ex) {
            Logger lgr = Logger.getLogger(Database.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);
        }
    }

    public ArrayList getTeamList(String aCountry) {
        ArrayList teamList = new ArrayList();
        Team team;
        int counter = SelectInt("SELECT Count(*) FROM Zespoly WHERE Kraj='" + aCountry + "'");        
        for (int i = 0; i < counter; i++) {
            int id = SelectInt("SELECT Id FROM Zespoly WHERE Kraj='" + aCountry + "'" + "LIMIT " + i + "," + 1);
            String name = SelectString("SELECT Nazwa FROM Zespoly WHERE Kraj='" + aCountry + "'" + "LIMIT " + i + "," + 1);
            String league = SelectString("SELECT Liga FROM Zespoly WHERE Kraj='" + aCountry + "'" + "LIMIT " + i + "," + 1);            
            team = new Team(id,name,aCountry,league,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);
            teamList.add(team);
        }
        return teamList;
    }

    public ArrayList getPlayerList(int aTeam) {
        ArrayList playerList = new ArrayList();
        Player player;
        Calendar c = Calendar.getInstance();
        String club = SelectString("SELECT Nazwa FROM Zespoly WHERE Id='" + aTeam + "'");
        int counter = SelectInt("SELECT Count(*) FROM Zawodnicy WHERE Id_Klubu='" + aTeam + "'");
        for (int i = 0; i < counter; i++) {
            int id = SelectInt("SELECT Id FROM Zawodnicy WHERE Id_Klubu='" + aTeam + "'" + "LIMIT " + i + "," + 1);
            String name = SelectString("SELECT Imie FROM Zawodnicy WHERE Id_Klubu='" + aTeam + "'" + "LIMIT " + i + "," + 1);
            String surname = SelectString("SELECT Nazwisko FROM Zawodnicy WHERE Id_Klubu='" + aTeam + "'" + "LIMIT " + i + "," + 1);
            String nationality = SelectString("SELECT Narodowosc FROM Zawodnicy WHERE Id_Klubu='" + aTeam + "'" + "LIMIT " + i + "," + 1);
            int age = c.get(Calendar.YEAR)-SelectInt("SELECT YEAR(Data_urodzenia) FROM Zawodnicy WHERE Id_Klubu='" + aTeam + "'" + "LIMIT " + i + "," + 1);
            int number = SelectInt("SELECT Numer FROM Zawodnicy WHERE Id_Klubu='" + aTeam + "'" + "LIMIT " + i + "," + 1);
            String position = SelectString("SELECT Pozycja FROM Zawodnicy WHERE Id_Klubu='" + aTeam + "'" + "LIMIT " + i + "," + 1);
            int height = SelectInt("SELECT Wzrost FROM Zawodnicy WHERE Id_Klubu='" + aTeam + "'" + "LIMIT " + i + "," + 1);
            int weight = SelectInt("SELECT Waga FROM Zawodnicy WHERE Id_Klubu='" + aTeam + "'" + "LIMIT " + i + "," + 1);
            int attRange = SelectInt("SELECT Zasieg_atak FROM Zawodnicy WHERE Id_Klubu='" + aTeam + "'" + "LIMIT " + i + "," + 1);
            int defRange = SelectInt("SELECT Zasieg_obrona FROM Zawodnicy WHERE Id_Klubu='" + aTeam + "'" + "LIMIT " + i + "," + 1);
            player = new Player(id, name, surname, nationality, age, number, club, position, height, weight, attRange, defRange,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0);
            playerList.add(player);
        }
        return playerList;
    }
    
    public ArrayList getHallList(String aCountry){
        ArrayList hallList = new ArrayList();
        Hall hall;
        int counter = SelectInt("SELECT Count(*) FROM Hale WHERE Kraj='"+aCountry+"'");
        for (int i = 0; i < counter; i++) {
            int id = SelectInt("SELECT Id FROM Hale WHERE Kraj='"+aCountry+"' LIMIT "+i+","+1);
            String name = SelectString("SELECT Nazwa FROM Hale WHERE Kraj='"+aCountry+"' LIMIT "+i+","+1);
            String address = SelectString("SELECT Ulica FROM Hale WHERE Kraj='"+aCountry+"' LIMIT "+i+","+1);
            String city = SelectString("SELECT Miasto FROM Hale WHERE Kraj='"+aCountry+"' LIMIT "+i+","+1);
            int capacity = SelectInt("SELECT Pojemnosc FROM Hale WHERE Kraj='"+aCountry+"' LIMIT "+i+","+1);
            hall = new Hall(id, name, address, city, aCountry, capacity);
            hallList.add(hall);
        }
        return hallList;
    }
    
    public ArrayList getCoachList(int aTeam){
        ArrayList coachList = new ArrayList();
        Coach coach;
        Calendar c = Calendar.getInstance();
        int counter = SelectInt("SELECT Count(*) FROM Sztab_szkoleniowy WHERE Id_Klubu='" + aTeam + "'");
        for (int i = 0; i < counter; i++) {
            int id = SelectInt("SELECT Id FROM Sztab_szkoleniowy WHERE Id_Klubu='" + aTeam + "'" + "LIMIT " + i + "," + 1);
            String name = SelectString("SELECT Imie FROM Sztab_szkoleniowy WHERE Id_Klubu='" + aTeam + "'" + "LIMIT " + i + "," + 1);
            String surname = SelectString("SELECT Nazwisko FROM Sztab_szkoleniowy WHERE Id_Klubu='" + aTeam + "'" + "LIMIT " + i + "," + 1);
            int age = c.get(Calendar.YEAR)-SelectInt("SELECT YEAR(Data_urodzenia) FROM Sztab_szkoleniowy WHERE Id_Klubu='" + aTeam + "'" + "LIMIT " + i + "," + 1);
            coach = new Coach(id,name,surname,age);
            coachList.add(coach);
        }
        return coachList;
    }
    
    public ArrayList getRefereeList(){
        ArrayList refereeList = new ArrayList();
        Referee referee;
        Calendar c = Calendar.getInstance();
        int counter = SelectInt("SELECT Count(*) FROM Sedziowie");
        for (int i = 0; i < counter; i++) {
            int id = SelectInt("SELECT Id FROM Sedziowie LIMIT " + i + "," + 1);
            String name = SelectString("SELECT Imie FROM Sedziowie LIMIT " + i + "," + 1);
            String surname = SelectString("SELECT Nazwisko FROM Sedziowie LIMIT " + i + "," + 1);
            int age = c.get(Calendar.YEAR)-SelectInt("SELECT YEAR(Data_urodzenia) FROM Sedziowie LIMIT " + i + "," + 1);
            referee = new Referee(id,name,surname,age);
            refereeList.add(referee);
        }
        return refereeList;
    }


    public void updateTable(int aTeam, int aYear, boolean aWin, int aSetsWin, int aSetsLost, int aPointsWin, int aPointsLost) {
        connect();
        String query;
        if (aWin) {
            query = "UPDATE Zespoly SET Mecze_rozegrane=Mecze_rozegrane+1, Mecze_wygrane=Mecze_wygrane+1, Punkty=Punkty+3, Sety_wygrane=Sety_wygrane+" + aSetsWin + ", Sety_przegrane=Sety_przegrane+" + aSetsLost + ", Punkty_zdobyte=Punkty_zdobyte+" + aPointsWin + ", Punkty_stracone=Punkty_stracone+" + aPointsLost + " WHERE Id='" + aTeam + "' AND Sezon='" + aYear + "'";
        } else {
            query = "UPDATE Zespoly SET Mecze_rozegrane=Mecze_rozegrane+1, Mecze_przegrane=Mecze_przegrane+1, Sety_wygrane=Sety_wygrane+" + aSetsWin + ", Sety_przegrane=Sety_przegrane+" + aSetsLost + ", Punkty_zdobyte=Punkty_zdobyte+" + aPointsWin + ", Punkty_stracone=Punkty_stracone+" + aPointsLost + " WHERE Id='" + aTeam + "' AND Sezon='" + aYear + "'";
        }
        try {
            pst.executeUpdate(query);
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            disconnect();
        }
    }

    public void updateResults(int aDay, int aMonth, int aYear, int aHall, int aHome, int aAway, int aSetsHome, int aSetsAway) {
        String query ="INSERT INTO Wyniki(Data, Id_Hali, Id_Gospodarze, Id_Goscie, Sety_gospodarze, Sety_goscie) values ( '"+aYear+"-"+(aMonth+1)+"-"+aDay+"', "+aHall+", "+aHome+", "+ aAway+", "+aSetsHome+", "+aSetsAway+" )";
        connect();
        try {
            pst.executeUpdate(query);
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            disconnect();
        }
    }
    
    public void updatePlayerStats(int aDay, int aMonth, int aYear,Player aPlayer){
       String query ="INSERT INTO Statystyki_zawodnikow values ( '"+aYear+"-"+(aMonth+1)+"-"+aDay+"', "+aPlayer.id+", "+aPlayer.successAttacks+", "+ aPlayer.blockedAttacks+", "+aPlayer.failedAttacks+", "+aPlayer.successBlocks+", "+aPlayer.defendedBlocks+", "+aPlayer.failedBlocks+", "+aPlayer.serveAces+", "+aPlayer.receivedServes+", "+aPlayer.failedServes+", "+aPlayer.receivesToNet+", "+aPlayer.bouncedReceives+", "+aPlayer.failedReceives+", "+aPlayer.defencesNet+", "+aPlayer.bouncedDefences+", "+aPlayer.failedDefences+", "+aPlayer.singleBlockPasses+", "+aPlayer.tripleBlockPasses+", "+aPlayer.failedPasses+" )";
        connect();
        try {
            pst.executeUpdate(query);
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            disconnect();
        }
    }
    
    public void updateTeamStats(int aDay, int aMonth, int aYear, Team aTeam){
        String query ="INSERT INTO Statystyki_zespolow values ( '"+aYear+"-"+(aMonth+1)+"-"+aDay+"', "+aTeam.id+", "+aTeam.successAttacks+", "+ aTeam.blockedAttacks+", "+aTeam.failedAttacks+", "+aTeam.successBlocks+", "+aTeam.defendedBlocks+", "+aTeam.failedBlocks+", "+aTeam.serveAces+", "+aTeam.receivedServes+", "+aTeam.failedServes+", "+aTeam.receivesToNet+", "+aTeam.bouncedReceives+", "+aTeam.failedReceives+", "+aTeam.defencesNet+", "+aTeam.bouncedDefences+", "+aTeam.failedDefences+", "+aTeam.singleBlockPasses+", "+aTeam.tripleBlockPasses+", "+aTeam.failedPasses+" )";
        connect();
        try {
            pst.executeUpdate(query);
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            disconnect();
        }
    }
    
    public void updateRefereeStats(int aDay, int aMonth, int aYear,Referee aReferee, double aRate){
        String query ="INSERT INTO Statystyki_sedziow values ( '"+aYear+"-"+(aMonth+1)+"-"+aDay+"', "+aReferee.id+", "+aRate+" )";
        connect();
        try {
            pst.executeUpdate(query);
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            disconnect();
        }
    }
    
    public void updateHallStats(int aDay, int aMonth, int aYear,Hall aHall, int aAttendance){
        String query ="INSERT INTO Statystyki_hali values ( '"+aYear+"-"+(aMonth+1)+"-"+aDay+"', "+aHall.id+", "+aAttendance+" )";
        connect();
        try {
            pst.executeUpdate(query);
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            disconnect();
        }
    }
    
    public void insertPlayer(String aName, String aSurname, String aBirthday, String aNationality, String aPosition, int aNumber, int aIdClub, int aHeight, int aWeight, int aAttRange, int aDefRange) {
        String atributes = aName+","+aSurname+","+aBirthday+","+aNationality+","+aPosition+","+aNumber+","+aIdClub+","+aHeight+","+aWeight+","+aAttRange+","+aDefRange;
        String query ="INSERT INTO Zawodnicy(Imie,Nazwisko,Data_urodzenia,Narodowosc,Pozycja,Numer,Id_Klubu,Wzrost,Waga,Zasieg_atak,Zasieg_obrona) values (atributes)";
        connect();
        try {
            pst.executeUpdate(query);
        } catch (SQLException ex) {
            Logger.getLogger(Database.class.getName()).log(Level.SEVERE, null, ex);
        } finally {
            disconnect();
        }
    }

    public int SelectInt(String query) {
        ResultSet rs;
        int result = 0;
        connect();
        try {
            rs = pst.executeQuery(query);
            while (rs.next()) {
                result = rs.getInt(1);
            }
        } catch (SQLException ex) {
            Logger lgr = Logger.getLogger(Database.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);
        } finally {
            disconnect();
        }
        return result;
    }

    public String SelectString(String query) {
        ResultSet rs;
        String result = null;
        connect();
        try {
            rs = pst.executeQuery(query);
            while (rs.next()) {
                result = rs.getString(1);
            }
        } catch (SQLException ex) {
            Logger lgr = Logger.getLogger(Database.class.getName());
            lgr.log(Level.SEVERE, ex.getMessage(), ex);
        } finally {
            disconnect();
        }
        return result;
    }
    /* PRZYKŁADY UŻYCIA
     * 
     * public static void main (String args[]){
        ArrayList teamList = getTeamList("Polska");
        Team team = (Team) teamList.get(0);
        Team team2 = (Team) teamList.get(1);
        System.out.println(team.league);
        ArrayList playerList = getPlayerList(team.id);
    Player player = (Player) playerList.get(0);
    player.blockedAttacks = 5;
    team.blockedAttacks = 5;
        System.out.println(player.age);
        ArrayList hallList = getHallList("Polska");
        Hall hall = (Hall) hallList.get(0);
        System.out.println(hall.capacity);
        ArrayList coachList = getCoachList(team.id);
        Coach coach = (Coach) coachList.get(0);
        System.out.println(coach.id);
        ArrayList refereeList = getRefereeList();
        Referee referee = (Referee) refereeList.get(0);
        System.out.println(referee.name);
        int year = Calendar.getInstance().get(Calendar.YEAR);
        System.out.println(year);
        int month = Calendar.getInstance().get(Calendar.MONTH);
        System.out.println(month);
        int day = Calendar.getInstance().get(Calendar.DAY_OF_MONTH);
        System.out.println(day);
        updateTable(1,2012,true,3,0,75,60);
        updateResults(day,month,year,hall.id,team.id,team2.id,3,0);
        updatePlayerStats(day,month,year,player);
        updateTeamStats(day,month,year,team);
        updateRefereeStats(day,month,year,referee,5.5);
        updateHallStats(day,month,year,hall,2000);
    }*/
}
